package com.bwhx.eruler.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.springframework.web.multipart.commons.CommonsMultipartFile;

import com.bwhx.eruler.bean.casdilly.CasdillyCustomer;

public class ReadExcelUtil {

	public static Log log = LogFactory.getLog(ReadExcelUtil.class);

	/**
	 * 对外提供读取excel 的方法
	 */
	public static HashMap<String, Object> readExcel(File file, Integer staffId, String fenchangid) throws IOException {
		String fileName = file.getName();
		String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName.substring(fileName.lastIndexOf(".") + 1);
		if ("xls".equals(extension)) {
			return read2003Excel(file, staffId, fenchangid);
		} /*
			 * else if ("xlsx".equals(extension)) { return read2007Excel(file); }
			 */ else {
			throw new IOException("不支持的文件类型");
		}
	}

	/**
	 * 读取 office 2003 excel
	 * 
	 * @throws IOException
	 * @throws FileNotFoundException
	 */
	private static HashMap<String, Object> read2003Excel(File file, Integer staffId, String fenchangid)
			throws IOException {
		HashMap<String, Object> resultMap = new HashMap<String, Object>();
		List<List<Object>> list = new LinkedList<List<Object>>();
		HSSFWorkbook hwb = new HSSFWorkbook(new FileInputStream(file));
		HSSFSheet sheet = hwb.getSheetAt(0);
		Object value = null;
		HSSFRow row = null;
		HSSFCell cell = null;
		int counter = 0;
		int rowCount = sheet.getPhysicalNumberOfRows();
		for (int i = sheet.getFirstRowNum(); counter < rowCount; i++) {
			row = sheet.getRow(i);
			if (row == null) {
				resultMap.put("status", "0");
				resultMap.put("msg", (i + 1) + "行为空，请删除。");
				return resultMap;
				// continue;
			} else {
				counter++;
			}
			List<Object> linked = new LinkedList<Object>();
			for (int j = 0; j < 12; j++) {
				cell = row.getCell(j);
				if (cell == null) {
					// [编号, 单位, 部门, 分部门, 班组, 岗位, 姓名, 性别, 职务, 电话, 年龄, 备注]
					if (j == 6) {
						resultMap.put("status", "0");
						resultMap.put("msg", (i + 1) + "行" + (j + 1)
								+ "列为空。<br><br>*若数据不完整，请补全。<br><br><p style='line-height:20px;font-weight:bold;'>*若数据完整，请新建电子表格文档，并将数据粘贴过去，粘贴数据时使用<font color='red'>“选择性粘贴”</font>，在弹出框中点选<font color='red'>“数值”</font>选项。</p>");
						return resultMap;
					} else {
						linked.add(null);
						continue;
					}
				}
				DecimalFormat df = new DecimalFormat("0");// 格式化 number String
															// 字符
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
				DecimalFormat nf = new DecimalFormat("0");// 格式化数字

				switch (cell.getCellType()) {
				case XSSFCell.CELL_TYPE_STRING:
					log.info(i + "行" + j + " 列 is String type");
					value = cell.getStringCellValue();
					break;
				case XSSFCell.CELL_TYPE_NUMERIC:
					log.info(
							i + "行" + j + " 列 is Number type ; DateFormt:" + cell.getCellStyle().getDataFormatString());
					if ("@".equals(cell.getCellStyle().getDataFormatString())) {
						value = df.format(cell.getNumericCellValue());
					} else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
						value = nf.format(cell.getNumericCellValue());
					} else {
						value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
					}
					break;
				case XSSFCell.CELL_TYPE_BOOLEAN:
					log.info(i + "行" + j + " 列 is Boolean type");
					value = cell.getBooleanCellValue();
					break;
				case XSSFCell.CELL_TYPE_BLANK:
					log.info(i + "行" + j + " 列 is Blank type");
					value = "";
					break;
				default:
					log.info(i + "行" + j + " 列 is default type");
					value = cell.toString();
					resultMap.put("status", "0");
					resultMap.put("msg", (i + 1) + "行" + (j + 1) + "列是无法识别的数值。<br><br>*请检查内容是否有公式或者引用。<br><br>");
					return resultMap;
				}

				/*
				 * if (j<4&&(value == null || "".equals(value.toString()))) {
				 * resultMap.put("status", "0"); resultMap.put("msg", (i+1)+"行"+(j+1)
				 * +"列为空。<br><br>*若有数据请补全。<br><br>*若无数据，请选中当前行，右击删除。<br><br><p style='line-height:20px'>*若末尾行反复报此错误，请选中文件末尾可视区域所有空行右击删除 （行中应该有空格或者样式被程序误读为数据）。</p>"
				 * ); return resultMap; }
				 */
				linked.add(value);
			}
			list.add(linked);
			log.info(linked);
			if (i == 0) {
				String titleString = linked.toString();
				if (!titleString.equals("[编号, 单位, 部门, 分部门, 班组, 岗位, 姓名, 性别, 职务, 电话, 年龄, 备注]")) {
					resultMap.put("status", "0");
					resultMap.put("msg", "请检查第一行（表头）是否为：<br>[编号, 单位, 部门, 分部门, 班组, 岗位, 姓名, 性别, 职务, 电话, 年龄, 备注]");
					return resultMap;
				}

			}
		}

		LinkedList<CasdillyCustomer> customerList = new LinkedList<CasdillyCustomer>();
		// [编号, 单位, 部门,分部门,岗位,班组, 姓名, 性别, 职务, 电话, 年龄, 备注]
		for (int i = 1; i < list.size(); i++) {
			List<Object> obj = list.get(i);
			CasdillyCustomer customer = new CasdillyCustomer(obj.get(0).toString().trim(), obj.get(1).toString().trim(),
					null == obj.get(2) ? null : obj.get(2).toString().trim(),
					null == obj.get(3) ? null : obj.get(3).toString().trim(),
					null == obj.get(4) ? null : obj.get(4).toString().trim(),
					null == obj.get(5) ? null : obj.get(5).toString().trim(), obj.get(6).toString().trim(),
					// obj.get(4).toString().trim().equals("男")?0:1,
					(null == obj.get(7) || "".equals(obj.get(7))) ? 0
							: (obj.get(7).toString().trim().equals("女") ? 1 : 0),
					null == obj.get(8) ? null : obj.get(8).toString().trim(),
					null == obj.get(9) ? null : obj.get(9).toString().trim(),
					null == obj.get(10) ? null : obj.get(10).toString().trim(),
					null == obj.get(11) ? null : obj.get(11).toString().trim());

			customer.setCreateTime(new Date());
			customer.setCreatorID(staffId.toString());
			customer.setContractID(fenchangid);

			customerList.add(customer);
		}

		resultMap.put("status", "1");
		resultMap.put("data", customerList);
		return resultMap;
	}

	public static List<HashMap<String, String>> readExcel(File file, String columnsIdentity) {
		List<HashMap<String, String>> data = new ArrayList<HashMap<String, String>>();
		if (file != null && columnsIdentity != null && !columnsIdentity.isEmpty()) {
			try {
				HSSFWorkbook hwb = new HSSFWorkbook(new FileInputStream(file));
				HSSFSheet sheet = hwb.getSheetAt(0);
				if (sheet != null) {
					int index = sheet.getFirstRowNum();
					HSSFRow row = sheet.getRow(index);
					if (row != null) {
						String[] array = columnsIdentity.split(",");
						if (array != null && array.length > 0) {
							StringBuilder stuColumn = new StringBuilder();
							StringBuilder column = new StringBuilder();
							for (int i = 0; i < array.length; i++) {
								stuColumn.append(array[i]);
								column.append(row.getCell(i).getStringCellValue());
							}
							String value = "";
							if (stuColumn.toString().equals(column.toString())) {
								for (int i = index + 1; i < sheet.getPhysicalNumberOfRows(); i++) {
									row = sheet.getRow(i);
									if (row != null) {
										HashMap<String, String> dict = new HashMap<String, String>();
										for (int j = 0; j < array.length; j++) {
											switch (row.getCell(j).getCellType()) {
											case Cell.CELL_TYPE_STRING:// String类型单元格
												value = row.getCell(j).getRichStringCellValue().getString();
												break;
											case Cell.CELL_TYPE_NUMERIC:// 数字类型
												if (DateUtil.isCellDateFormatted(row.getCell(j))) {
													// 输出日期
													value = String.valueOf(row.getCell(j).getDateCellValue());
												} else {
													// 输出数字
													value = String.valueOf(row.getCell(j).getNumericCellValue());
												}
												break;
											case Cell.CELL_TYPE_BOOLEAN:// Boolean类型
												value = String.valueOf(row.getCell(j).getBooleanCellValue());
												break;
											case Cell.CELL_TYPE_FORMULA:// 公式
												// 输出公式
												value = String.valueOf(row.getCell(j).getCellFormula());
												break;
											default:
											}
											dict.put(array[j],value.replace(".0", ""));
											value = "";
										}
										data.add(dict);
									} else {
										break;
									}
								}
							} else {
								data.add(setErrorMsg(String.format("excel文档表头不符合标准，要求是:%s,实际是:%s", stuColumn.toString(),
										column.toString())));
							}
						} else {
							data.add(setErrorMsg("列名无效"));
						}
					} else {
						data.add(setErrorMsg("excel文档中的没有有效数据"));
					}
				} else {
					data.add(setErrorMsg("excel文档中的sheet无效"));
				}
			} catch (Exception ex) {
				data.add(setErrorMsg(ex.getMessage()));
			}
		}
		return data;
	}

	private static HashMap<String, String> setErrorMsg(String msg) {
		HashMap<String, String> dict = new HashMap<String, String>();
		dict.put("error", msg);
		return dict;
	}

}
